package com.eascs.overdue.dao;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;

@Repository
public class O2oOverdueDao extends BaseO2oDao {
	  /**
     * @Title: queryOverdue
     * @Description: 根据住申请人、配偶身份证查询他们的逾期数据
     * @author 张庆奇
     * @param idnums
     * @return
     * @return O2oOverdueReport
     * @throws
     * @date 2017年1月9日 上午11:30:01
     */
    public  PageResult<Object[]> queryOverdue(String idnums){
    	StringBuilder sql = new StringBuilder();
    	String str = "";
    	if (StringUtils.isNotEmpty(idnums)) {
    		StringBuilder sql_str = new StringBuilder();
			String[] idNumbers = idnums.split(",");
			for (int i = 0; i < idNumbers.length; i++) {
				sql_str.append("'").append(idNumbers[i]).append("'").append(",");
			}
			str=sql_str.toString();
			str = str.substring(0, sql_str.length() - 1);
		}
    	 sql.append("SELECT op.bill_code as billcode,op.loaner_name as loanername,");
    	 sql.append("sum(op.pay_amount) as pay_amount,sum(op.payment_amount) as payment_amount,");
    	 sql.append("(sum(op.pay_amount)-sum(op.payment_amount)) as yue,");
    	 sql.append("NULL as defaultamount");
    	 sql.append(" FROM  o2o_ea_loan_data op ");
    	 sql.append(" where op.loan_type in ('DC','LXDC')  ");
    	 sql.append(" and op.loaner_card in ("+str+") GROUP BY op.bill_code ");
    	 sql.append(" UNION ");
    	 sql.append(" SELECT oor.billcode as billcode,oor.loanername as loanername,");
    	 sql.append(" null as pay_amount,NULL as payment_amount,");
    	 sql.append(" NULL as yue,");
    	 sql.append("  sum(oor.defaultamount) ");
    	 sql.append(" from o2o_overdue_report oor ");
    	 sql.append(" where  oor.isnew='Y' and oor.status in (1,2) ");
    	 sql.append(" and oor.idcard in ("+str+") GROUP BY oor.billcode ");
//    	if (StringUtils.isNotEmpty(idnums)) {
//			String[] idNumbers = idnums.split(",");
//			sql.append(" and op.loaner_card in  (");
//			for (int i = 0; i < idNumbers.length; i++) {
//				sql.append("'").append(idNumbers[i]).append("'").append(",");
//			}
//			sql_str = sql.toString();
//			sql_str = sql_str.substring(0, sql_str.length() - 1) + ") GROUP BY op.bill_code ";
//		}
    	SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 100, 0);		
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	
    }
	

}
